-- CONFIGURATION
SET DEFINE OFF;

-- TABLE CREATIONS
CREATE TABLE CATEGORIES
(
  CATEGORY_ID     NUMBER,
  CATEGORY_NAME   NVARCHAR2(50)     NOT NULL,
  CONSTRAINT  CATEGORIES_PK 
    PRIMARY KEY (CATEGORY_ID)
);

CREATE TABLE PRODUCTS
(
  PRODUCT_ID      NUMBER,
  PRODUCT_NAME    NVARCHAR2(50)     NOT NULL,
  PRICE           NUMBER(5,2)       NOT NULL,
  DESCRIPTION     NVARCHAR2(255),
  LAST_UPDATE     DATE,
  CATEGORY_ID     NUMBER            NOT NULL,
  CONSTRAINT  PRODUCTS_PK  
    PRIMARY KEY (PRODUCT_ID),
  CONSTRAINT  CATEGORY_ID_FK 
    FOREIGN KEY (CATEGORY_ID) REFERENCES CATEGORIES (CATEGORY_ID)
);

CREATE TABLE CUSTOMERS
(
  CUSTOMER_ID   NUMBER,
  FIRST_NAME    NVARCHAR2(50)   NOT NULL,
  LAST_NAME     NVARCHAR2(50)   NOT NULL,
  EMAIL         NVARCHAR2(255)  NOT NULL,
  PHONE_NUMBER  VARCHAR(45)        NOT NULL,
  ADDRESS       NVARCHAR2(255)  NOT NULL,
  CITY          NVARCHAR2(50)   NOT NULL,
  CC_NUMBER     VARCHAR(19)     NOT NULL,
  CONSTRAINT  CUSTOMERS_PK  
    PRIMARY KEY (CUSTOMER_ID),
  CONSTRAINT  EMAIL_UQ  
    UNIQUE (EMAIL)
);

CREATE TABLE CUSTOMER_ORDERS
(
  CUSTOMER_ORDER_ID     NUMBER,
  AMOUNT                NUMBER(6,2)   NOT NULL,
  DATE_CREATED          DATE          DEFAULT SYSDATE,
  CUSTOMER_ID           NUMBER        NOT NULL,
  CONFIRMATION_NUMBER   NUMBER,
  CONSTRAINT  CUSTOMER_ORDERS_PK 
    PRIMARY KEY (CUSTOMER_ORDER_ID),
  CONSTRAINT  CUSTOMER_ID_FK
    FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS (CUSTOMER_ID)
);

CREATE TABLE ORDERED_PRODUCTS
(
  CUSTOMER_ORDER_ID     NUMBER,
  PRODUCT_ID            NUMBER,
  QUANTITY              NUMBER(6),
  CONSTRAINT ORDERED_PRODUCT_PK
    PRIMARY KEY (CUSTOMER_ORDER_ID, PRODUCT_ID),
  CONSTRAINT  CUSTOMER_ORDER_ID_FK
    FOREIGN KEY (CUSTOMER_ORDER_ID) REFERENCES CUSTOMERS (CUSTOMER_ID),
  CONSTRAINT  PRODUCT_ID_FK
    FOREIGN KEY (PRODUCT_ID)  REFERENCES PRODUCTS (PRODUCT_ID)
);

-- SEQUENCE CREATIONS
CREATE SEQUENCE CATEGORY_SEQ;
CREATE SEQUENCE PRODUCT_SEQ;
CREATE SEQUENCE CUSTOMER_SEQ;
CREATE SEQUENCE CUSTOMER_ORDERS_SEQ;

-- DATA GENERATION
INSERT INTO CATEGORIES VALUES (CATEGORY_SEQ.NEXTVAL, 'dairy');
INSERT INTO CATEGORIES VALUES (CATEGORY_SEQ.NEXTVAL, 'meats');
INSERT INTO CATEGORIES VALUES (CATEGORY_SEQ.NEXTVAL, 'bakery');
INSERT INTO CATEGORIES VALUES (CATEGORY_SEQ.NEXTVAL, 'fruit & veg');

INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, PRICE,DESCRIPTION, CATEGORY_ID) VALUES (PRODUCT_SEQ.NEXTVAL, 'milk',1.7,'semi skimmed (1L)',1);
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, PRICE,DESCRIPTION, CATEGORY_ID) VALUES (PRODUCT_SEQ.NEXTVAL, 'cheese',2.39,'mild cheddar (330g)',1);
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, PRICE,DESCRIPTION, CATEGORY_ID) VALUES (PRODUCT_SEQ.NEXTVAL, 'butter',1.09,'unsalted (250g)',1);
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, PRICE,DESCRIPTION, CATEGORY_ID) VALUES (PRODUCT_SEQ.NEXTVAL, 'free range eggs',1.76,'medium-sized (6 eggs)',1);
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, PRICE,DESCRIPTION, CATEGORY_ID) VALUES (PRODUCT_SEQ.NEXTVAL, 'organic meat patties',2.29,'rolled in fresh herbs<br>2 patties (250g)',2);
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, PRICE,DESCRIPTION, CATEGORY_ID) VALUES (PRODUCT_SEQ.NEXTVAL, 'parma ham',3.49,'matured  organic (70g)',2);
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, PRICE,DESCRIPTION, CATEGORY_ID) VALUES (PRODUCT_SEQ.NEXTVAL, 'chicken leg',2.59,'free range (250g)',2);
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, PRICE,DESCRIPTION, CATEGORY_ID) VALUES (PRODUCT_SEQ.NEXTVAL, 'sausages',3.55,'reduced fat  pork<br>3 sausages (350g)',2);
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, PRICE,DESCRIPTION, CATEGORY_ID) VALUES (PRODUCT_SEQ.NEXTVAL, 'sunflower seed loaf',1.89,'600g',3);
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, PRICE,DESCRIPTION, CATEGORY_ID) VALUES (PRODUCT_SEQ.NEXTVAL, 'sesame seed bagel',1.19,'4 bagels',3);
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, PRICE,DESCRIPTION, CATEGORY_ID) VALUES (PRODUCT_SEQ.NEXTVAL, 'pumpkin seed bun',1.15,'4 buns',3);
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, PRICE,DESCRIPTION, CATEGORY_ID) VALUES (PRODUCT_SEQ.NEXTVAL, 'chocolate cookies',2.39,'contain peanuts<br>(3 cookies)',3);
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, PRICE,DESCRIPTION, CATEGORY_ID) VALUES (PRODUCT_SEQ.NEXTVAL, 'corn on the cob',1.59,'2 pieces',4);
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, PRICE,DESCRIPTION, CATEGORY_ID) VALUES (PRODUCT_SEQ.NEXTVAL, 'red currants',2.49,'150g',4);
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, PRICE,DESCRIPTION, CATEGORY_ID) VALUES (PRODUCT_SEQ.NEXTVAL, 'broccoli',1.29,'500g',4);
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, PRICE,DESCRIPTION, CATEGORY_ID) VALUES (PRODUCT_SEQ.NEXTVAL, 'seedless watermelon',1.49,'250g',4);

INSERT INTO CUSTOMERS VALUES (CUSTOMER_SEQ.NEXTVAL, 'Charlie', 'Pace', 'c.pace@youareeverybody.com', '605434778', 'airoka 45', '1', '4224311324421331');
INSERT INTO CUSTOMERS VALUES (CUSTOMER_SEQ.NEXTVAL, 'MC', 'Hammer', 'hammer@hammertime.com', '226884562', 'Ruska 11', '2', '4321123443211234');
INSERT INTO CUSTOMERS VALUES (CUSTOMER_SEQ.NEXTVAL, 'Karel', 'Gott', 'gott@karelgott.com', '224517995', 'Kostelna 83', '7', '3311332222444411');
INSERT INTO CUSTOMERS VALUES (CUSTOMER_SEQ.NEXTVAL, 'Helena', 'Vondraa?kova', 'h.vondrackova@seznam.cz', '224517995', 'Letohradska 18', '7', '1111222244443333');
INSERT INTO CUSTOMERS VALUES (CUSTOMER_SEQ.NEXTVAL, 'Sawyer', 'Ford', 'sawyer.ford@gmail.com', '204888845', 'Duana 87', '1', '2222333311114444');
INSERT INTO CUSTOMERS VALUES (CUSTOMER_SEQ.NEXTVAL, 'Dalibor', 'Janda', 'dalibor@dalibor.cz', '728331184', 'Krkonoaska 9', '3', '3111444222212334');
INSERT INTO CUSTOMERS VALUES (CUSTOMER_SEQ.NEXTVAL, 'Richard', 'Genzer', 'r.genzer@nova.cz', '737610775', 'Plzeaska 131', '5', '2244443321123311');
INSERT INTO CUSTOMERS VALUES (CUSTOMER_SEQ.NEXTVAL, 'Iveta', 'Bartoaova', 'i.bartosova@volny.cz', '734556133', 'Prokopska 60', '1', '3333111144442222');
INSERT INTO CUSTOMERS VALUES (CUSTOMER_SEQ.NEXTVAL, 'Jin-Soo', 'Kwon', 'jin.kwon@hotmail.kr', '606338909', 'Ve Staeaovia?kach 49', '6', '1111222233334444');
INSERT INTO CUSTOMERS VALUES (CUSTOMER_SEQ.NEXTVAL, 'Benjamin', 'Linus', 'b.linus@lost.com', '222756448', 'Druastevna 77', '4', '4444222233331111');
INSERT INTO CUSTOMERS VALUES (CUSTOMER_SEQ.NEXTVAL, 'Leoa', 'Marea', 'mares@ferrari.it', '608995383', 'Paaaaska 89', '1', '2222444411113333');
INSERT INTO CUSTOMERS VALUES (CUSTOMER_SEQ.NEXTVAL, 'John', 'Locke', 'maninblack@lostpedia.com', '413443727', 'Valea?ovska 20', '9', '2244331133114422');
INSERT INTO CUSTOMERS VALUES (CUSTOMER_SEQ.NEXTVAL, 'Lucie', 'Bala', 'lucie@jampadampa.cz', '733556813', 'Na hajku 3', '8', '3333444422221111');
INSERT INTO CUSTOMERS VALUES (CUSTOMER_SEQ.NEXTVAL, 'Sayid', 'Jarrah', 'sayid@gmail.com', '602680793', 'Kodaaska 78', '10', '5490123456789128');
INSERT INTO CUSTOMERS VALUES (CUSTOMER_SEQ.NEXTVAL, 'Hugo', 'Reyes', 'hurley@mrcluck.com', '605449336', 'aerotanova 64', '3', '4539992043491562');

INSERT INTO CUSTOMER_ORDERS VALUES (CUSTOMER_ORDERS_SEQ.NEXTVAL, 16.50, DEFAULT, 15, 285434339);
INSERT INTO CUSTOMER_ORDERS VALUES (CUSTOMER_ORDERS_SEQ.NEXTVAL, 16.11, DEFAULT, 14, 428278565);
INSERT INTO CUSTOMER_ORDERS VALUES (CUSTOMER_ORDERS_SEQ.NEXTVAL, 26.00, DEFAULT, 13, 503113888);
INSERT INTO CUSTOMER_ORDERS VALUES (CUSTOMER_ORDERS_SEQ.NEXTVAL, 17.63, DEFAULT, 12, 916407556);
INSERT INTO CUSTOMER_ORDERS VALUES (CUSTOMER_ORDERS_SEQ.NEXTVAL, 17.24, DEFAULT, 11, 189191209);
INSERT INTO CUSTOMER_ORDERS VALUES (CUSTOMER_ORDERS_SEQ.NEXTVAL, 15.57, DEFAULT, 10, 274027361);
INSERT INTO CUSTOMER_ORDERS VALUES (CUSTOMER_ORDERS_SEQ.NEXTVAL, 4.49,  DEFAULT, 9, 250764732);
INSERT INTO CUSTOMER_ORDERS VALUES (CUSTOMER_ORDERS_SEQ.NEXTVAL, 19.70, DEFAULT, 8, 766244032);
INSERT INTO CUSTOMER_ORDERS VALUES (CUSTOMER_ORDERS_SEQ.NEXTVAL, 37.49, DEFAULT, 7, 53395157);
INSERT INTO CUSTOMER_ORDERS VALUES (CUSTOMER_ORDERS_SEQ.NEXTVAL, 18.90, DEFAULT, 6, 818358116);
INSERT INTO CUSTOMER_ORDERS VALUES (CUSTOMER_ORDERS_SEQ.NEXTVAL, 18.92, DEFAULT, 5, 244956320);
INSERT INTO CUSTOMER_ORDERS VALUES (CUSTOMER_ORDERS_SEQ.NEXTVAL, 17.66, DEFAULT, 4, 868642371);
INSERT INTO CUSTOMER_ORDERS VALUES (CUSTOMER_ORDERS_SEQ.NEXTVAL, 10.22, DEFAULT, 3, 344549009);
INSERT INTO CUSTOMER_ORDERS VALUES (CUSTOMER_ORDERS_SEQ.NEXTVAL, 12.16, DEFAULT, 2, 475644436);
INSERT INTO CUSTOMER_ORDERS VALUES (CUSTOMER_ORDERS_SEQ.NEXTVAL, 10.75, DEFAULT, 1, 247455344);

INSERT INTO ORDERED_PRODUCTS VALUES (15, 1, 1);
INSERT INTO ORDERED_PRODUCTS VALUES (15, 15, 3);
INSERT INTO ORDERED_PRODUCTS VALUES (15, 3, 2);
INSERT INTO ORDERED_PRODUCTS VALUES (14, 5, 4);
INSERT INTO ORDERED_PRODUCTS VALUES (13, 13, 1);
INSERT INTO ORDERED_PRODUCTS VALUES (13, 4, 1);
INSERT INTO ORDERED_PRODUCTS VALUES (13, 10, 2);
INSERT INTO ORDERED_PRODUCTS VALUES (13, 16, 1);
INSERT INTO ORDERED_PRODUCTS VALUES (12, 1, 3);
INSERT INTO ORDERED_PRODUCTS VALUES (12, 12, 4);
INSERT INTO ORDERED_PRODUCTS VALUES (11, 13, 1);
INSERT INTO ORDERED_PRODUCTS VALUES (11, 2, 2);
INSERT INTO ORDERED_PRODUCTS VALUES (11, 9, 1);
INSERT INTO ORDERED_PRODUCTS VALUES (11, 14, 2);
INSERT INTO ORDERED_PRODUCTS VALUES (11, 16, 1);
INSERT INTO ORDERED_PRODUCTS VALUES (11, 10, 1);
INSERT INTO ORDERED_PRODUCTS VALUES (10, 13, 10);
INSERT INTO ORDERED_PRODUCTS VALUES (9, 8, 5);
INSERT INTO ORDERED_PRODUCTS VALUES (9, 7, 2);
INSERT INTO ORDERED_PRODUCTS VALUES (9, 6, 2);
INSERT INTO ORDERED_PRODUCTS VALUES (9, 5, 2);
INSERT INTO ORDERED_PRODUCTS VALUES (8, 8, 2);
INSERT INTO ORDERED_PRODUCTS VALUES (8, 15, 2);
INSERT INTO ORDERED_PRODUCTS VALUES (8, 11, 1);
INSERT INTO ORDERED_PRODUCTS VALUES (8, 9, 1);
INSERT INTO ORDERED_PRODUCTS VALUES (8, 14, 1);
INSERT INTO ORDERED_PRODUCTS VALUES (8, 16, 1);
INSERT INTO ORDERED_PRODUCTS VALUES (7, 16, 1);
INSERT INTO ORDERED_PRODUCTS VALUES (6, 15, 2);
INSERT INTO ORDERED_PRODUCTS VALUES (6, 9, 1);
INSERT INTO ORDERED_PRODUCTS VALUES (6, 4, 2);
INSERT INTO ORDERED_PRODUCTS VALUES (6, 6, 1);
INSERT INTO ORDERED_PRODUCTS VALUES (6, 3, 1);
INSERT INTO ORDERED_PRODUCTS VALUES (5, 15, 1);
INSERT INTO ORDERED_PRODUCTS VALUES (5, 7, 5);
INSERT INTO ORDERED_PRODUCTS VALUES (4, 8, 1);
INSERT INTO ORDERED_PRODUCTS VALUES (4, 1, 1);
INSERT INTO ORDERED_PRODUCTS VALUES (4, 11, 1);
INSERT INTO ORDERED_PRODUCTS VALUES (4, 14, 2);
INSERT INTO ORDERED_PRODUCTS VALUES (4, 4, 1);
INSERT INTO ORDERED_PRODUCTS VALUES (4, 16, 1);
INSERT INTO ORDERED_PRODUCTS VALUES (3, 1, 1);
INSERT INTO ORDERED_PRODUCTS VALUES (3, 8, 6);
INSERT INTO ORDERED_PRODUCTS VALUES (2, 13, 1);
INSERT INTO ORDERED_PRODUCTS VALUES (2, 5, 1);
INSERT INTO ORDERED_PRODUCTS VALUES (2, 15, 1);
INSERT INTO ORDERED_PRODUCTS VALUES (2, 2, 1);
INSERT INTO ORDERED_PRODUCTS VALUES (2, 11, 2);
INSERT INTO ORDERED_PRODUCTS VALUES (2, 16, 1);
INSERT INTO ORDERED_PRODUCTS VALUES (2, 4, 1);
INSERT INTO ORDERED_PRODUCTS VALUES (1, 12, 1);
INSERT INTO ORDERED_PRODUCTS VALUES (1, 2, 2);
INSERT INTO ORDERED_PRODUCTS VALUES (1, 13, 1);
INSERT INTO ORDERED_PRODUCTS VALUES (1, 10, 1);
INSERT INTO ORDERED_PRODUCTS VALUES (1, 8, 1);

COMMIT;


